﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data;

namespace WorkNodeFramework
{
    public class SqlDataProvider:DataProvider
    {
        private const string PRIMARY_KEY_COLUMNS =
            @"SELECT
                KCU.COLUMN_NAME as ColumnName, 
                TC.CONSTRAINT_TYPE as ConstraintType 
            FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
            JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
            ON KCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME
            WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND KCU.TABLE_NAME='{0}'";
       
        public SqlDataProvider()
            : base()
        { }

        public SqlDataProvider(string connectionString) 
            :base(connectionString)
        { 
        }

        public override DbConnection CreateConnection()
        {
            return CreateConnection(DefaultConnectionString);
        }

        public override DbConnection CreateConnection(string connectionString)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = connectionString;
            conn.Open();
            return conn;
        }

        public override string GetParameterName(string baseName)
        {
            return "@" + baseName;
        }

        public override DbParameter CreateParameter(string parameterName,object value)
        {
            return new SqlParameter(parameterName, value);
        }

        public override DbCommand CreateCommand()
        {
            return new SqlCommand();
        }

        public override DbDataAdapter CreateDataAdapter()
        {
            return new SqlDataAdapter();
        }

        public override string QueryExpressioṇ̣̣̣̣́(IQueryExpression queryExpression)
        {
            if (queryExpression is Column)
            {
                Column col = (Column)queryExpression;
                if (!string.IsNullOrEmpty(col.ColumnAlias))
                    return col.FullName + " AS [" + col.ColumnAlias + "]";
                return col.FullName;
            }
            else if (queryExpression is AllColumnQueryExpression)
                return ((AllColumnQueryExpression)queryExpression).Table.FullName + ".*";
            else if (queryExpression is StringQueryExpression)
                return ((StringQueryExpression)queryExpression).Expression;
            throw new NotSupportedException();
        }

        public override string QualifyTableName(ITable table)
        {
            return "[" + table.Name + "]";
        }

        public override string QualifyColumnName(Column column)
        {
            return QualifyTableName(column.Table) + "." + "[" + column.Name + "]";
        }

        internal override string GenerateJoinClause(IJoinClause join)
        {
            StringBuilder sb = new StringBuilder();
            string tableName = QualifyTableName(join.Table);
            switch (join.JoinType)
            { 
                case JoinType.CrossJoin:
                    sb.Append("CROSS JOIN ");
                    sb.Append(tableName);
                    break;
                case JoinType.InnerJoin:
                case JoinType.LeffOuterJoin:
                case JoinType.RightOuterJoin:
                case JoinType.FullOuterJoin:
                    switch(join.JoinType)
                    {
                        case JoinType.InnerJoin:
                            sb.Append("INNER JOIN ");
                            break;
                        case JoinType.LeffOuterJoin:
                            sb.Append("LEFT OUTER JOIN ");
                            break;
                        case JoinType.RightOuterJoin:
                            sb.Append("RIGHT OUTER JOIN ");
                            break;
                        case JoinType.FullOuterJoin:
                            sb.Append("FULL OUTER JOIN ");
                            break;
                    }
                    sb.Append(tableName);
                    sb.Append(" ON ");
                    AbstractJoinClause ajoin = (AbstractJoinClause)join;
                    string fromTableColumn = QueryExpressioṇ̣̣̣̣́(ajoin.FromTableColumn);
                    string joinTableColumn = QueryExpressioṇ̣̣̣̣́(ajoin.JoinTableColumn);
                    sb.Append(fromTableColumn);
                    sb.Append(" = ");
                    sb.Append(joinTableColumn);
                    break;
            }
            return sb.ToString();
        }

        internal override string GenerateWhereComplexCondition(IWhereCondition complexCondition, ParameterIndexCollection parameters)
        {
            StringBuilder sb = new StringBuilder();
            if (complexCondition is WhereCondition)
                sb.Append(GenerateWhereCondition((WhereCondition)complexCondition, parameters));
            else{
                sb.Append("(");
                bool isNotCondition = false;
                string logical = " AND ";
                if (complexCondition is OrWhereCondition)
                    logical = " OR ";
                else if (complexCondition is NotWhereCondition)
                {
                    isNotCondition = true;
                    logical = " NOT ";
                }

                IWhereCondition condition=null;
                IWhereCondition[] conditions = complexCondition.Conditions;
                if (isNotCondition) 
                {
                    sb.Append(logical);
                    condition = conditions[0];
                    sb.Append(GenerateWhereComplexCondition(condition, parameters));
                }
                else
                {
                    for (int i = 0; i < conditions.Length - 1; i++)
                    {
                        condition = conditions[i];
                        sb.Append(GenerateWhereComplexCondition(condition, parameters));
                        sb.Append(logical);
                    }
                    condition = conditions[conditions.Length - 1];
                    sb.Append(GenerateWhereComplexCondition(condition, parameters));
                }
                sb.Append(")");
            }
            return sb.ToString();
        }

        internal override string GenerateWhereCondition(WhereCondition condition, ParameterIndexCollection parameters)
        {
            Column col = condition.Column;
            string colFullName = QualifyColumnName(col);
            StringBuilder sb = new StringBuilder();
            sb.Append("(");
            if (condition.Comparision != WhereComparision.Between &&
                condition.Comparision != WhereComparision.In &&
                condition.Comparision != WhereComparision.NotIn)
            {
                ParameterIndex pindex = parameters.SetParameterIndex(col.Name, condition.Value);
                string parName = GetParameterName(pindex.BaseName + pindex.Index.ToString());

                switch (condition.Comparision)
                {
                    case WhereComparision.NotEquals:
                        sb.Append("NOT ");
                        sb.Append(colFullName);
                        sb.Append(" = ");
                        sb.Append(parName);
                        break;
                    case WhereComparision.Greater:
                        sb.Append(colFullName);
                        sb.Append(" > ");
                        sb.Append(parName);
                        break;
                    case WhereComparision.EqualsOrGreater:
                        sb.Append(colFullName);
                        sb.Append(" >= ");
                        sb.Append(parName);
                        break;
                    case WhereComparision.Leaster:
                        sb.Append(colFullName);
                        sb.Append(" < ");
                        sb.Append(parName);
                        break;
                    case WhereComparision.EqualsOrLeaster:
                        sb.Append(colFullName);
                        sb.Append(" <= ");
                        sb.Append(parName);
                        break;
                    case WhereComparision.Like:
                        sb.Append(colFullName);
                        sb.Append(" LIKE ");
                        sb.Append(parName);
                        break;
                    case WhereComparision.IsNull:
                        sb.Append(colFullName);
                        sb.Append(" is NULL");
                        break;
                    default:
                        sb.Append(colFullName);
                        sb.Append(" = ");
                        sb.Append(parName);
                        break;
                }
            }
            else
            {
                int len = 0;
                string token = ", ";
                object[] values = condition.Value as object[];
                Select select = null;
                if (values == null)
                    select = (Select)condition.Value;
                sb.Append(colFullName);
                if (condition.Comparision == WhereComparision.Between)
                {
                    sb.Append(" BETWEEN ");
                    token = "AND ";
                }
                else
                {
                    if (condition.Comparision == WhereComparision.In)
                        sb.Append(" IN (");
                    else
                        sb.Append(" NOT IN (");
                }

                if (values != null)
                {
                    ParameterIndex _pIndex = null;

                    string _pName = null;
                    len = values.Length;
                    for (int i = 0; i < len - 1; i++)
                    {
                        _pIndex = parameters.SetParameterIndex(col.Name, values[i]);
                        _pName = GetParameterName(_pIndex.BaseName + _pIndex.Index.ToString());
                        sb.Append(_pName + token);
                    }
                    len = len - 1;
                    _pIndex = parameters.SetParameterIndex(col.Name, values[len]);
                    _pName = GetParameterName(_pIndex.BaseName + _pIndex.Index.ToString());
                    sb.Append(_pName);
                }
                else
                {
                    string selQuery = BuildSelectQueryStatement(select, parameters);
                    sb.Append(selQuery);
                }

                if (condition.Comparision != WhereComparision.Between)
                    sb.Append(")");
            }
            sb.Append(")");
            return sb.ToString();
        }

        internal override string BuildSelectQueryStatement<T>(SelectQuery<T> select, ParameterIndexCollection parameters)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ");
            //=========================
            bool countExp = (select is Select) && (select as Select).GetRecordCount;
            if (countExp)
                sb.Append("COUNT(*) ");
            else
            {
                if (select.WithDistinct)
                    sb.Append("DISTINCT ");
                if (select.WithTop > -1)
                    sb.Append("TOP " + select.WithTop.ToString() + " ");
                //=====================
                //Query expressions
                if (select.QueryList.Count == 0)
                {
                    sb.Append(select.TableList[0].All.Expression);
                    if (select.JoinClauses.Count == 0)
                        sb.Append(" ");
                    else
                    {
                        foreach (IJoinClause join in select.JoinClauses)
                        {
                            sb.Append(", ");
                            sb.Append(join.Table.All.Expression + " ");
                        }
                    }
                }
                else if (select.QueryList.Count == 1)
                    sb.Append(QueryExpressioṇ̣̣̣̣́(select.QueryList[0]) + " ");
                else
                {
                    IQueryExpression queryExpression = null;
                    for (int i = 0; i < select.QueryList.Count - 1; i++)
                    {
                        queryExpression = select.QueryList[i];
                        sb.Append(QueryExpressioṇ̣̣̣̣́(queryExpression) + ", ");
                    }
                    queryExpression = select.QueryList[select.QueryList.Count - 1];
                    sb.Append(QueryExpressioṇ̣̣̣̣́(queryExpression) + " ");
                }
            }
            //=========================
            if (select is SelectInto)
            {
                SelectInto si = select as SelectInto;
                sb.Append("INTO ");
                sb.Append(QualifyTableName(si.IntoTable)+" ");
            }

            sb.Append(BuildQueryStatement(select,parameters));

            if (select.OrderList.Count > 0)
            {
                sb.Append(" ORDER BY ");
                foreach (IOrderBy order in select.OrderList)
                    sb.Append(order.Expression+", ");
                sb.Remove(sb.Length - 2, 2);
            }
            return sb.ToString();
        }

        internal override string BuildDeleteQueryStatement(Delete delete, ParameterIndexCollection parameters)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("DELETE ");
            sb.Append(BuildQueryStatement(delete, parameters));
            return sb.ToString();
        }

        internal override string BuildUpdateQueryStatement(Update update, ParameterIndexCollection parameters)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("UPDATE ");
            sb.Append(QualifyTableName(update.UpdatedTable) + " ");
            sb.Append("SET ");
            foreach (KeyValuePair<Column, object> kv in update.SetList)
            {
                ParameterIndex pi = parameters.SetParameterIndex(kv.Key.Name, kv.Value);
                string pname = GetParameterName(pi.BaseName + pi.Index.ToString());
                sb.Append(QualifyColumnName(kv.Key));
                sb.Append(" = ");
                sb.Append(pname);
                sb.Append(", ");
            }
            int idx = sb.Length - 2;
            sb.Remove(idx, 2);
            sb.Append(" ");
            sb.Append(BuildQueryStatement(update,parameters));
            return sb.ToString();
        }

        internal override string BuildQueryStatement(Query query, ParameterIndexCollection parameters)
        {
            StringBuilder sb = new StringBuilder();
            //From section
            if (query.TableList.Count > 0)
            {
                sb.Append("FROM ");
                sb.Append(QualifyTableName(query.TableList[0]) + " ");
            }
            //Join clauses
            if (query.JoinClauses.Count > 0) {
                foreach (IJoinClause join in query.JoinClauses) { 
                    sb.Append(GenerateJoinClause(join));
                    sb.Append(" ");
                }
            }
            //Where clause
            if (query.Conditions.Count > 0)
            {
                sb.Append("WHERE ");
                if (query.Conditions.Count == 1)
                    sb.Append(GenerateWhereComplexCondition(query.Conditions[0], parameters));
                else
                {
                    AndWhereCondition andCondition = new AndWhereCondition(query.Conditions.ToArray());
                    sb.Append(GenerateWhereComplexCondition(andCondition,parameters));
                }
            }
            return sb.ToString();
        }

        internal override string BuildInsertQueryStatement(Insert insert, ParameterIndexCollection parameters)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("INSERT ");
            sb.Append(QualifyTableName(insert.Table) + " ");
            sb.Append("(");
            foreach(Column col in insert.Columns){
                string colName = QualifyColumnName(col);
                sb.Append(colName+", ");
            }
            sb.Remove(sb.Length-2,2);
            sb.Append(")");
            sb.Append(" VALUES (");

            for (int i = 0; i < insert.Columns.Count; i++)
            {
                Column col = insert.Columns[i];
                object val = insert.Values[i];
                ParameterIndex pi = parameters.SetParameterIndex(col.Name, val);
                string parName = GetParameterName(pi.BaseName + pi.Index.ToString());
                sb.Append(parName + ", ");
            }
            sb.Remove(sb.Length - 2, 2);
            sb.Append(")");
            if (insert.ResultRequired)
                sb.Append(" SELECT SCOPE_IDENTITY() AS newID");
            return sb.ToString();
        }

        internal override string BuildInsertIntoQueryStatement(InsertInto insert, ParameterIndexCollection parameters)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("INSERT INTO ");
            sb.Append(QualifyTableName(insert.Table)+" ");
            sb.Append(BuildSelectQueryStatement(insert.SelectQuery, parameters));
            return sb.ToString();
        }

        TableProvider tableProvider;
        public override TableProvider TableProvider
        {
            get {
                if (tableProvider == null)
                    tableProvider = new SqlTableProvider(this);
                return tableProvider;
            }
        }

        internal override ITable LoadTable(string name) {
            return LoadTable<Table>(name);
        }

        internal override T LoadTable<T>(string name)
        {
            T table = (T)Activator.CreateInstance(typeof(T), this, name);
            table.Columns.Clear();
            string cmdText = new Select(this)
                .From(table)
                .BuildSelectQueryStatement();

            DataTable primaryKeys = ExecuteDataTable(string.Format(PRIMARY_KEY_COLUMNS, name));
            primaryKeys.PrimaryKey = new DataColumn[] { primaryKeys.Columns["ColumnName"] };

            DataTable data = GetSchema(cmdText);
            foreach (DataRow dr in data.Rows)
            {
                string colName = (string)dr[SqlSchemaVariable.COLUMN_NAME];
                Column col = new Column(table, colName);
                col.IsPrimaryKey = primaryKeys.Rows.Find(colName) != null;
                UpdateAdditionalInformation(col, dr);
                table.Columns.Add(col);
            }

            return table;
        }

        void UpdateAdditionalInformation(Column col, DataRow schemaRow)
        {
            DataRow dr = schemaRow;
            col.IsNullable = (bool)dr[SqlSchemaVariable.ALLOW_DBNULL];
            col.SqlDbType = TableProvider.GetSqlDbType((string)dr["DataTypeName"]);
            col.Size = (int)dr["ColumnSize"];
        }
    }
}
